![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
The V$LIBRARYCACHE table contains statistics on how well you are using the library cache. The important columns to view in this table are PINS and RELOADS:
A few number of reloads relative to the number of executions indicates a high cache-hit rate. To get an idea of the total number of cache misses, use this statement: SQL> SELECT SUM(reloads) "Cache Misses", 2 SUM(pins) "Executions", 3 100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent" 4 FROM v$librarycache; Cache Misses Executions Cache Miss Percent ------- -------- ------------ 9 2017 .44620724 The preceding example indicates that a sum of 2,017 SQL statements, PL/SQL blocks, and object definitions were accessed and only 9 were reloaded because they had aged out of the library cache. This means that only 0.44 percent of these statements resulted in reparsinga very good cache-hit ratio. To look at the cache hits based on the types of statements, use the following statement: SQL> SELECT namespace, 2 reloads "Cache Misses", 3 pins "Executions" 4 FROM v$librarycache; NAMESPACE Cache Misses Executions -------- -------- -------- SQL AREA 4 1676 TABLE/PROCEDURE 5 309 BODY 0 0 TRIGGER 0 0 INDEX 0 21 CLUSTER 0 15 OBJECT 0 0 PIPE 0 0 8 rows selected. The total number of reloads should be near zero. If you see more than 1 percent library cache misses, you should take action: reduce the cache misses by writing identical SQL statements or by increasing the size of the library cache. You should be able to reduce the library cache misses by increasing the amount of memory available for the library cache. Do this by increasing the Oracle tunable parameter SHARED_POOL_SIZE. You may also need to increase the number of cursors available for a session by increasing the Oracle parameter OPEN_CURSORS. Be careful not to increase the amount of memory required beyond that set aside by the operating system. Any paging or swapping caused by that offsets any advantage you get from the library cache. If you have plenty of memory, you may be able to speed access to the shared SQL areas by setting the Oracle initialization parameter CURSOR_SPACE_FOR_TIME to TRUE. When this parameter is set to TRUE, it specifies that a shared SQL area cannot be deallocated until all the cursors associated with it are closed. If CURSOR_SPACE_FOR_TIME is TRUE, it is not necessary for Oracle to check to see whether the SQL statement is in the library cache because it cannot be deallocated as long as the cursor is open. If memory is scarce on your system, do not set this parameter. If the value is TRUE and there is no space in the shared pool for a new SQL statement, an error is returned, halting the application. Now that you have reviewed the advantages of using stored procedures, functions, and packages, the following sections present some specifics on how to use them. Procedures and FunctionsProcedures and functions are similar. In fact, they are so much alike that, throughout this book (except for this chapter), they have been referred to indiscriminately as stored procedures. Procedures and functions are subprograms made up of PL/SQL code that take a set of parameters given to them by the calling program and perform a set of actions. The difference between a procedure and a function is that a function can include a return value. Both functions and procedures can modify and return data passed to them as a parameter. Usually, procedures are used unless only one return value is needed. A procedure or function that has been stored in the library cache is referred to as a stored procedure or a stored function. A stored procedure or stored function has the following properties:
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |